{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Association rule mining\n",
    "\"\"\"\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = os.getcwd()\n",
    "files = os.listdir(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "\n",
    "Key ideas to report in the paper\n",
    "1. General statistics: Number of pipes with defects, most frequent defects \n",
    "2. Number of instances of two defects being within the same vicinity, probabilities of these occurrences\n",
    "3. Support, lift, etc.\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Helper functions\n",
    "\"\"\"\n",
    "\n",
    "def change_defect_names(df_cond):\n",
    "    \"\"\" \n",
    "    Change the names of defects: e.g., FL => Fracture, TB => Tap\n",
    "    Takes df_cond as input. \n",
    "    \"\"\"\n",
    "    deposit_codes = ['DAE', 'DAGS', 'DAR', 'DAZ', 'DSV', 'DSGV', 'DSC', 'DSZ', 'DNF', 'DNGV', 'DNZ']\n",
    "    deformed_codes = ['DR', 'DFBR', 'DFBI', 'DFC', 'DFE', 'DTBR', 'DTBI']\n",
    "    infiltration_codes = ['IS', 'ISB', 'ISJ', 'ISC', 'ISL', 'IW', 'IWB', 'IWC', 'IWJ', 'IWL', 'ID', 'IDB', 'IDC', 'IDJ', 'IDL', 'IR', 'IRB', 'IRC', 'IRJ', 'IRL', 'IG', 'IGB', 'IGC', 'IGL', 'IGJ' ]\n",
    "    hole_codes = ['HSV', 'HVV']\n",
    "    fracture_codes = ['FL', 'FC', 'FM', 'FS', 'FH']\n",
    "    crack_codes = ['CL', 'CC', 'CM', 'CS', 'CH']\n",
    "    broken_codes = ['BSV', 'BVV']\n",
    "    collapse_codes = ['X']\n",
    "    \n",
    "    df_cond.loc[df_cond['PACP_Code'].isin(deposit_codes), 'PACP_Code'] = 'Deposit'\n",
    "    df_cond.loc[df_cond['PACP_Code'].isin(deformed_codes), 'PACP_Code'] = 'Deformed'\n",
    "    df_cond.loc[df_cond['PACP_Code'].isin(infiltration_codes), 'PACP_Code'] = 'Infiltration'\n",
    "    df_cond.loc[df_cond['PACP_Code'].isin(hole_codes), 'PACP_Code'] = 'Hole'\n",
    "    df_cond.loc[df_cond['PACP_Code'].isin(fracture_codes), 'PACP_Code'] = 'Fracture'\n",
    "    df_cond.loc[df_cond['PACP_Code'].isin(crack_codes), 'PACP_Code'] = 'Crack'\n",
    "    df_cond.loc[df_cond['PACP_Code'].isin(broken_codes), 'PACP_Code'] = 'Broken'\n",
    "    df_cond.loc[df_cond['PACP_Code'].isin(collapse_codes), 'PACP_Code'] = 'Collapse'\n",
    "    \n",
    "    df_cond.loc[df_cond['PACP_Code'].str.startswith('T'), 'PACP_Code'] = 'Tap'\n",
    "    df_cond.loc[df_cond['PACP_Code'].str.startswith('A'), 'PACP_Code'] = 'Manhole'\n",
    "    df_cond.loc[df_cond['PACP_Code'].str.startswith('J'), 'PACP_Code'] = 'Joint Offset'\n",
    "    df_cond.loc[df_cond['PACP_Code'].str.startswith('R'), 'PACP_Code'] = 'Root'\n",
    "    \n",
    "    return df_cond\n",
    "\n",
    "def delete_rows(df_cond, keep_defects):\n",
    "    \"\"\"\n",
    "    Delete rows where the defects that we want to consider are not present\n",
    "    \"\"\"\n",
    "    df_cond = df_cond[df_cond['PACP_Code'].isin(keep_defects)]\n",
    "    return df_cond\n",
    "    \n",
    "\n",
    "def select_df(df, insp_id, defect_list):\n",
    "    \"\"\"\n",
    "    Helper function used by count_defect_pairs()\n",
    "    This function only keeps the rows where ['defect1', 'defect2'] are present\n",
    "    It helps speed up the computation\n",
    "    \"\"\"\n",
    "    df = df[(df['InspectionID'] == insp_id)]\n",
    "    return  df[df['PACP_Code'].isin(defect_list)]\n",
    "\n",
    "def delete_inspections_with_no_defects(df_cond, defects):\n",
    "    \"\"\"\n",
    "    Delete those inspections where there aren't any defects at all. \n",
    "    By defects we mean fractures, cracks, etc. Not tap and manhole\n",
    "    \"\"\"\n",
    "    insp_ids = np.unique(list(df_cond['InspectionID']))\n",
    "\n",
    "    keep_ids = [] # Inspection IDs that we want to keep\n",
    "\n",
    "    for insp_id in insp_ids:\n",
    "        df_temp = df_cond[(df_cond['InspectionID'] == insp_id)]\n",
    "        if df_temp['PACP_Code'].isin(defects).sum() > 0:\n",
    "            keep_ids.append(insp_id)\n",
    "    \n",
    "    return df_cond[df_cond['InspectionID'].isin(keep_ids)]\n",
    "\n",
    "def count_defect_pairs(df_cond, keep_defects, distance_threshold):\n",
    "    \"\"\"\n",
    "    Count the number of defect pairs that are < distance_threshold within each other\n",
    "    \"\"\"\n",
    "    defect_pair_counts = []\n",
    "    \n",
    "    for a in range(0, len(keep_defects)): \n",
    "        for b in range(a+1, len(keep_defects)):\n",
    "\n",
    "            pair_count = 0\n",
    "            defect_pair = [keep_defects[a], keep_defects[b]] # Pair of defects whose no. of occurrences we wish to calculate\n",
    "\n",
    "            for insp_id in df_cond['InspectionID'].unique():\n",
    "            \n",
    "            # Inspection id which we are counting for\n",
    "                df = select_df(df_cond, insp_id, defect_pair)\n",
    "                insp_id = list(df['InspectionID'])\n",
    "                dist = list(df['Distance'])\n",
    "                defects = list(df['PACP_Code']) # Defects which are selected from a particular inspection\n",
    "\n",
    "                for i in range (0, len(defects) - 1):\n",
    "                    for j in range (i+1, len(defects)):\n",
    "                        defect_1, defect_2 = defects[i], defects[j]\n",
    "                        if (defect_1 == defect_pair[0] and defect_2 == defect_pair[1]) or (defect_2 == defect_pair[0] and defect_1 == defect_pair[1]):\n",
    "                            if abs(dist[i] - dist[j]) <= distance_threshold:\n",
    "                                pair_count = pair_count + 1\n",
    "\n",
    "            defect_pair_counts.append([keep_defects[a], keep_defects[b], pair_count])\n",
    "            \n",
    "    return defect_pair_counts\n",
    "\n",
    "\n",
    "def calculate_length_of_pipeline(df_cond):\n",
    "    # Find total length of pipeline\n",
    "    insps = df_cond['InspectionID'].unique()\n",
    "    length = 0.0\n",
    "    for insp in insps:\n",
    "        df_temp = df_cond[df_cond['InspectionID'] == insp]\n",
    "        length += df_temp['Distance'].max()\n",
    "    return length\n",
    "\n",
    "\n",
    "def create_econometric_database(df_insp, cols, defects):\n",
    "    df_insp = df_insp[cols]\n",
    "\n",
    "    f, c, t, m, j, r, d, b, h, cp = [], [], [], [], [], [], [], [], [], []\n",
    "\n",
    "    insp_ids = list(df_insp['InspectionID'])\n",
    "\n",
    "    for insp_id in insp_ids:\n",
    "\n",
    "        df_temp = df_cond.loc[df_cond['InspectionID'] == insp_id]\n",
    "\n",
    "        f.append(len(df_temp[df_temp['PACP_Code'] == 'Fracture']))\n",
    "        c.append(len(df_temp[df_temp['PACP_Code'] == 'Crack']))\n",
    "        t.append(len(df_temp[df_temp['PACP_Code'] == 'Tap']))\n",
    "        m.append(len(df_temp[df_temp['PACP_Code'] == 'Manhole']))\n",
    "        j.append(len(df_temp[df_temp['PACP_Code'] == 'Joint Offset']))\n",
    "        r.append(len(df_temp[df_temp['PACP_Code'] == 'Root']))\n",
    "        d.append(len(df_temp[df_temp['PACP_Code'] == 'Deposit']))\n",
    "        b.append(len(df_temp[df_temp['PACP_Code'] == 'Broken']))\n",
    "        h.append(len(df_temp[df_temp['PACP_Code'] == 'Hole']))\n",
    "        cp.append(len(df_temp[df_temp['PACP_Code'] == 'Collapse']))\n",
    "\n",
    "    df_insp['Fracture'] = f\n",
    "    df_insp['Crack'] = c\n",
    "    df_insp['Tap'] = t\n",
    "    df_insp['Manhole'] = m\n",
    "    df_insp['Joint Offset'] = j\n",
    "    df_insp['Root'] = r\n",
    "    df_insp['Deposit'] = d\n",
    "    df_insp['Broken'] = b\n",
    "    df_insp['Hole'] = h\n",
    "    df_insp['Collapse'] = cp\n",
    "    \n",
    "    return df_insp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cluster_size = 1\n",
    "\n",
    "# Read the database of condition information from CSV\n",
    "df_cond = pd.read_csv('Conditions_Taylors.csv', sep=',')\n",
    "df_cond.head();\n",
    "print(\"Total number of inspections to begin with are: {}\".format(df_cond['InspectionID'].max()))\n",
    "\n",
    "# Change names of defects\n",
    "df_cond = change_defect_names(df_cond)\n",
    "\n",
    "# Keep only the rows which contain these defects. Delete all else\n",
    "keep_defects_struct = ['Fracture', 'Crack', 'Joint Offset', 'Broken', 'Hole', 'Collapse']\n",
    "keep_defects_operat = ['Root', 'Deposit']\n",
    "\n",
    "df_cond = delete_rows(df_cond, keep_defects_struct)\n",
    "\n",
    "df_cond = delete_inspections_with_no_defects(df_cond, keep_defects_struct)\n",
    "print(\"Length : {}\".format(calculate_length_of_pipeline(df_cond)))\n",
    "\n",
    "\n",
    "# Count the number of inspections that remain after the deletion\n",
    "print(\"\\nNumber of inspections which contain defects is: {}\".format(df_cond['InspectionID'].nunique()))\n",
    "\n",
    "# List the number of defects\n",
    "print(\"\\nNumber of defects is as follows: \\n{}\".format(df_cond['PACP_Code'].value_counts()))\n",
    "\n",
    "\"\"\"\n",
    "Counting only Fracture and Tap\n",
    "Delete all inspections which do not have both Fracture and Tap\n",
    "\"\"\"\n",
    "\n",
    "keep_defects = ['Joint Offset', 'Fracture']\n",
    "\n",
    "# Deletes those inspections which don't have defect1 or defect2\n",
    "df_cond = delete_inspections_with_no_defects(df_cond, [keep_defects[0], keep_defects[1]])\n",
    "\n",
    "# Count the number of inspections that remain after the deletion\n",
    "print(\"\\nNumber of inspections with {} and {} is: {}\".format(keep_defects[0], keep_defects[1], df_cond['InspectionID'].nunique()))\n",
    "\n",
    "# List the number of defects\n",
    "print(\"\\nNumber of defects in these inspections is as follows: \\n{}\".format(df_cond['PACP_Code'].value_counts()))\n",
    "\n",
    "# Counting the number of defect clusters within 5 feet\n",
    "defect_pair_counts = count_defect_pairs(df_cond, keep_defects, cluster_size)\n",
    "\n",
    "print('\\nNumber of ({}, {}) clusters is: {}'.format(keep_defects[0], keep_defects[1], defect_pair_counts[0][2]))\n",
    "\n",
    "# Count the total length of pipe in this dataframe\n",
    "length = calculate_length_of_pipeline(df_cond)\n",
    "\n",
    "print(\"Length : {}\".format(length))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_defect1 = df_cond['PACP_Code'].value_counts()[keep_defects[0]]\n",
    "num_defect2 = df_cond['PACP_Code'].value_counts()[keep_defects[1]]\n",
    "print(\"Number of {} is {}\".format(keep_defects[0], num_defect1))\n",
    "print(\"Number of {} is {}\".format(keep_defects[1], num_defect2))\n",
    "\n",
    "spacing1 = length/num_defect1\n",
    "spacing2 = length/num_defect2\n",
    "\n",
    "print(\"Length: {}\".format(length))\n",
    "print(\"Average {} spacing: {} feet\".format(keep_defects[0], spacing1))\n",
    "print(\"Average {} spacing: {} feet\".format(keep_defects[1], spacing2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If Taps and Fractures were uniformly distributed, calculate the number of co-occurrences\n",
    "import numpy as np\n",
    "\n",
    "defect1 = [[keep_defects[0], i, 1] for i in np.arange(0, length, spacing1)]\n",
    "defect2 = [[keep_defects[1], i, 1] for i in np.arange(0, length, spacing2)]\n",
    "df_cond_fake = pd.DataFrame(defect1+defect2, columns = ['PACP_Code', 'Distance', 'InspectionID'])\n",
    "defect_pair_counts = count_defect_pairs(df_cond_fake, [keep_defects[0], keep_defects[1]], cluster_size)\n",
    "print('\\nNumber of ({}, {}) clusters is: {}'.format(keep_defects[0], keep_defects[1], defect_pair_counts[0][2]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Count number of defect hotspots\n",
    "\n",
    "TODO: Compare with uiform distribution/Weibull distribution/Try fitting curve\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Helper functions\n",
    "\n",
    "def delete_rows(df_cond, keep_defects):\n",
    "    \"\"\"\n",
    "    Delete rows where the defects that we want to consider are not present\n",
    "    \"\"\"\n",
    "    df_cond = df_cond[df_cond['PACP_Code'].isin(keep_defects)]\n",
    "    return df_cond\n",
    "    \n",
    "\n",
    "def select_df(df, insp_id, defect_list):\n",
    "    \"\"\"\n",
    "    Helper function used by count_defect_pairs()\n",
    "    This function only keeps the rows where ['defect1', 'defect2'] are present\n",
    "    It helps speed up the computation\n",
    "    \"\"\"\n",
    "    df = df[(df['InspectionID'] == insp_id)]\n",
    "    return  df[df['PACP_Code'].isin(defect_list)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Defect Codes\n",
    "\n",
    "deposit_codes = ['DAE', 'DAGS', 'DAR', 'DAZ', 'DSV', 'DSGV', 'DSC', 'DSZ', 'DNF', 'DNGV', 'DNZ']\n",
    "deformed_codes = ['DR', 'DFBR', 'DFBI', 'DFC', 'DFE', 'DTBR', 'DTBI']\n",
    "infiltration_codes = ['IS', 'ISB', 'ISJ', 'ISC', 'ISL', 'IW', 'IWB', 'IWC', 'IWJ', 'IWL', 'ID', 'IDB', 'IDC', 'IDJ', 'IDL', 'IR', 'IRB', 'IRC', 'IRJ', 'IRL', 'IG', 'IGB', 'IGC', 'IGL', 'IGJ' ]\n",
    "hole_codes = ['HSV', 'HVV']\n",
    "fracture_codes = ['FL', 'FC', 'FM', 'FS', 'FH', 'FH2', 'FH3', 'FH4']\n",
    "crack_codes = ['CL', 'CC', 'CM', 'CS', 'CH', 'CH2', 'CH3', 'CH4']\n",
    "broken_codes = ['BSV', 'BVV']\n",
    "collapse_codes = ['X']\n",
    "\n",
    "tap_codes = ['TB', 'TBI', 'TBD', 'TBC', 'TBA', 'TF', 'TFI', 'TFD', 'TFC', 'TFA', 'TFB', 'TR', 'TRI', 'TRD', 'TRC', 'TRA', 'TRB', 'TS', 'TSI', 'TSD', 'TSA', 'TSB']\n",
    "root_codes = ['RFB', 'RFL', 'RFC', 'RFJ', 'RMB', 'RML', 'RMC', 'RMJ', 'RBB', 'RBL', 'RBC', 'RBJ', 'RTB', 'RTL', 'RTC', 'RTJ']\n",
    "joint_offset_codes = ['JOS', 'JOM', 'JOL', 'JOSD', 'JOMD', 'JOLD', 'JSS', 'JSM', 'JSL', 'JAS', 'JAM', 'JAL']\n",
    "\n",
    "defects_all = deposit_codes+deformed_codes+infiltration_codes+hole_codes+fracture_codes+crack_codes+broken_codes+root_codes+joint_offset_codes+collapse_codes\n",
    "defects_struct = deformed_codes+hole_codes+fracture_codes+crack_codes+broken_codes+joint_offset_codes+collapse_codes\n",
    "defects_operat = root_codes + deposit_codes\n",
    "\n",
    "# keep_defects = defects_struct\n",
    "keep_defects = defects_struct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Helper functions\n",
    "\"\"\"\n",
    "\n",
    "def delete_rows(df_cond, keep_defects):\n",
    "    \"\"\"\n",
    "    Delete rows where the defects that we want to consider are not present\n",
    "    \"\"\"\n",
    "    df_cond = df_cond[df_cond['PACP_Code'].isin(keep_defects)]\n",
    "    return df_cond\n",
    "    \n",
    "\n",
    "def delete_inspections_with_no_defects(df_cond, defects):\n",
    "    \"\"\"\n",
    "    Delete those inspections where there aren't any defects at all. \n",
    "    By defects we mean fractures, cracks, etc. Not tap and manhole\n",
    "    \"\"\"\n",
    "    insp_ids = np.unique(list(df_cond['InspectionID']))\n",
    "\n",
    "    keep_ids = [] # Inspection IDs that we want to keep\n",
    "\n",
    "    for insp_id in insp_ids:\n",
    "        df_temp = df_cond[(df_cond['InspectionID'] == insp_id)]\n",
    "        if df_temp['PACP_Code'].isin(defects).sum() > 0:\n",
    "            keep_ids.append(insp_id)\n",
    "    \n",
    "    return df_cond[df_cond['InspectionID'].isin(keep_ids)]\n",
    "\n",
    "\n",
    "def calculate_length_of_pipeline(df_cond):\n",
    "    # Find total length of pipeline\n",
    "    insps = df_cond['InspectionID'].unique()\n",
    "    length = 0.0\n",
    "    for insp in insps:\n",
    "        df_temp = df_cond[df_cond['InspectionID'] == insp]\n",
    "        length += df_temp['Distance'].max()\n",
    "    return length"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of inspections to begin with are: 1872\n",
      "Number of inspections after deletion are: 938\n"
     ]
    }
   ],
   "source": [
    "# Count number of defect zones\n",
    "df_cond = pd.read_csv('data/PACP_databases/Conditions_Hazen_Sawyer.csv', sep=',')\n",
    "df_cond.head();\n",
    "print(\"Total number of inspections to begin with are: {}\".format(df_cond['InspectionID'].nunique()))\n",
    "\n",
    "# Change names of defects\n",
    "# df_cond = change_defect_names(df_cond)\n",
    "\n",
    "# Keep only the rows which contain these defects. Delete all else\n",
    "# keep_defects = ['Fracture', 'Crack', 'Joint Offset', 'Root', 'Deposit', 'Broken', 'Hole', 'Infiltration', 'Deformed']\n",
    "\n",
    "\n",
    "df_cond = delete_rows(df_cond, keep_defects)\n",
    "\n",
    "# Delete inspections which have no 'defects', i.e, fracture, crack, root, joint offset, deposit, broken\n",
    "df_cond = delete_inspections_with_no_defects(df_cond, keep_defects)\n",
    "\n",
    "print(\"Number of inspections after deletion are: {}\".format(df_cond['InspectionID'].nunique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Number of defects in these inspections is as follows: \n",
      "FM     2052\n",
      "CM     1861\n",
      "CL     1319\n",
      "CC     1033\n",
      "FC     1013\n",
      "FL      559\n",
      "JOM     161\n",
      "FS      106\n",
      "BSV      46\n",
      "BVV      42\n",
      "CS       29\n",
      "HSV      25\n",
      "JOL      24\n",
      "HVV      21\n",
      "JSM      10\n",
      "CH2       7\n",
      "JAM       7\n",
      "CH3       4\n",
      "FH2       4\n",
      "JSL       2\n",
      "FH3       1\n",
      "JAL       1\n",
      "Name: PACP_Code, dtype: int64\n",
      "Length : 148927.5001701849\n"
     ]
    }
   ],
   "source": [
    "# List the number of defects\n",
    "df_counts = df_cond['PACP_Code'].value_counts()\n",
    "print(\"\\nNumber of defects in these inspections is as follows: \\n{}\".format(df_counts))\n",
    "df_counts.to_csv('defect_counts.csv')\n",
    "\n",
    "# Count the total length of pipe in this dataframe\n",
    "length = calculate_length_of_pipeline(df_cond)\n",
    "print(\"Length : {}\".format(length))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "thresh = 3\n",
    "insps = df_cond['InspectionID'].unique()\n",
    "zones = []\n",
    "\n",
    "for insp in insps:\n",
    "    \n",
    "    df_temp = df_cond[df_cond['InspectionID'] == insp]\n",
    "    df_temp = df_temp.sort_values(by=[\"Distance\"])\n",
    "    indices = df_temp.index\n",
    "    defect_prev, defect_curr = \"\",\"\"\n",
    "    dist_prev, dist_curr = 0, 0\n",
    "    zone_curr = []\n",
    "    \n",
    "    for index in indices:\n",
    "        defect_curr = df_temp.at[index, 'PACP_Code'] # Defect code at current index\n",
    "        dist_curr = float(df_temp.at[index, 'Distance']) # Distance of defect at current index        \n",
    "    \n",
    "        if abs(dist_curr - dist_prev) < thresh:\n",
    "            zone_curr.append((insp, defect_curr, dist_curr))\n",
    "        else:           \n",
    "            zones.append(zone_curr)\n",
    "            zone_curr = []\n",
    "            zone_curr.append((insp, defect_curr, dist_curr))\n",
    "                        \n",
    "        dist_prev = dist_curr\n",
    "        defect_prev = defect_curr\n",
    "        \n",
    "    zones.append(zone_curr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[],\n",
       " [(1, 'JOL', 11.10000038)],\n",
       " [],\n",
       " [(6, 'FM', 21.10000038)],\n",
       " [(6, 'FL', 75.5)],\n",
       " [],\n",
       " [(24, 'FL', 101.4000015)],\n",
       " [],\n",
       " [(25, 'CC', 13.69999981)],\n",
       " [(25, 'CL', 19.5)]]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(zones)\n",
    "zones[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete empty zones\n",
    "zones = list(filter(lambda a: a!=[], zones))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of defects in clusters with min 5 defects is 48\n",
      "Total number of defects is 8327\n",
      "Max defects in a zone is 6\n",
      "Number of clusters 9\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "Calculate number of colocated defects\n",
    "\"\"\"\n",
    "\n",
    "colocated = 0\n",
    "total = 0\n",
    "max_colocated = 0\n",
    "num_def_in_cluster = 5\n",
    "filtered_zones = []\n",
    "\n",
    "for zone in zones:\n",
    "    if len(zone) >= num_def_in_cluster:\n",
    "        colocated += len(zone)\n",
    "        filtered_zones.append(zone)\n",
    "        if len(zone) > max_colocated:\n",
    "            max_colocated = len(zone)\n",
    "            max_zone = zone\n",
    "    total += len(zone)\n",
    "    \n",
    "print(f'Number of defects in clusters with min {num_def_in_cluster} defects is {colocated}')\n",
    "print(f'Total number of defects is {total}')\n",
    "print(f'Max defects in a zone is {max_colocated}')\n",
    "print(f'Number of clusters {len(filtered_zones)}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(917, 'CM', 21.70000076),\n",
       " (917, 'CM', 24.60000038),\n",
       " (917, 'CL', 27.39999962),\n",
       " (917, 'CM', 27.70000076),\n",
       " (917, 'FM', 27.70000076),\n",
       " (917, 'FM', 29.70000076)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max_zone"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "# Count number of zones which have fractures or cracks only\n",
    "num_zones_with_major_defects = 0\n",
    "\n",
    "\n",
    "for zone in filtered_zones:\n",
    "    for _, defect_code, _ in zone:\n",
    "        if defect_code in broken_codes + deformed_codes + hole_codes:\n",
    "            num_zones_with_major_defects += 1\n",
    "            break\n",
    "\n",
    "print(num_zones_with_major_defects)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_zones\n",
    "\n",
    "import csv\n",
    "\n",
    "with open(\"filered_zones.csv\", \"w\", newline=\"\") as f:\n",
    "    writer = csv.writer(f)\n",
    "    writer.writerows(filtered_zones)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{1: 6075, 2: 887, 3: 110, 4: 25, 5: 6, 6: 3}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "Calculate the number of zones of various lengths\n",
    "\"\"\"\n",
    "\n",
    "num_zones = {i:0 for i in range(1, max_colocated + 1)}\n",
    "for zone in zones:\n",
    "    if len(zone) >= 1:\n",
    "        num_zones[len(zone)] += 1\n",
    "        \n",
    "num_zones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[(1027, 'FM', 114.3000031),\n",
       "  (1027, 'FM', 116.09999850000001),\n",
       "  (1027, 'JOM', 117.90000149999999),\n",
       "  (1027, 'FM', 119.40000149999999),\n",
       "  (1027, 'FM', 122.09999850000001)],\n",
       " [(1713, 'FM', 228.89999390000003),\n",
       "  (1713, 'FM', 231.6999969),\n",
       "  (1713, 'FC', 234.0),\n",
       "  (1713, 'FC', 235.1999969),\n",
       "  (1713, 'FM', 236.89999390000003)],\n",
       " [(1763, 'FM', 46.5),\n",
       "  (1763, 'FM', 47.59999847),\n",
       "  (1763, 'FL', 49.59999847),\n",
       "  (1763, 'FM', 52.20000076),\n",
       "  (1763, 'FM', 54.79999924)]]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "Count the number of zones with more than 3 fractures\n",
    "\"\"\"\n",
    "hotspots = []\n",
    "count_hotspots = 0\n",
    "for zone in zones:\n",
    "    count_def = 0\n",
    "    if len(zone) >= 5:\n",
    "        for _, pacp_code, _ in zone:\n",
    "            if pacp_code in fracture_codes:\n",
    "                count_def += 1\n",
    "        if count_def > 3:\n",
    "            count_hotspots += 1\n",
    "            hotspots.append(zone)\n",
    "\n",
    "hotspots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Calculate severity scores\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "grades = {'JOM':1, 'JOL':2, 'JOMD':1, 'JOLD':2, 'JSM':1, 'JSL':2, 'JAM':1, 'JAL':2, 'X':5, 'BSV':5, 'BVV':5, 'DR':5, 'DFBR':5, 'DFBI':5, 'DFC':5, 'DFE':5, 'DTBR':5, 'DTBI':5, 'HSV':5, 'HVV':5, 'FL':3, 'FC':2, 'FM':4, 'FS':3, 'FH2':4, 'FH3':5, 'FH4':5, 'CL':2, 'CC':1, 'CM':3, 'CS':2, 'CH2':4, 'CH3':5, 'CH4':5}\n",
    "\n",
    "def calc_severity_zone(zone):\n",
    "    zone_length = 0\n",
    "    grade = 0\n",
    "    \n",
    "    for _, def_code, _ in zone:\n",
    "        grade += grades[def_code]\n",
    "    \n",
    "    num_defects = len(zone)\n",
    "    zone_length = zone[num_defects-1][2] - zone[0][2] # Length is distance of last - first\n",
    "    if zone_length < thresh:\n",
    "        zone_length = thresh\n",
    "    return grade/zone_length\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1\n",
      "2\n"
     ]
    }
   ],
   "source": [
    "x = [1,2,3]\n",
    "for i in range (0,len(x)):\n",
    "    print (i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average severity is 2.0054516703015848\n",
      "Maximum severity is 3.3333333333333335\n",
      "Minimum severity is 1.1428572011428604\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[[(163, 'FL', 56.59999847),\n",
       "  (163, 'CC', 58.70000076),\n",
       "  (163, 'FC', 59.29999924),\n",
       "  (163, 'FM', 60.59999847)],\n",
       " [(635, 'FC', 61.0),\n",
       "  (635, 'FC', 61.0),\n",
       "  (635, 'FC', 63.90000153),\n",
       "  (635, 'FC', 63.90000153)],\n",
       " [(685, 'CL', 112.09999850000001),\n",
       "  (685, 'FM', 112.3000031),\n",
       "  (685, 'FM', 114.5),\n",
       "  (685, 'CM', 117.3000031)],\n",
       " [(690, 'FM', 53.5),\n",
       "  (690, 'CM', 56.29999924),\n",
       "  (690, 'CM', 56.59999847),\n",
       "  (690, 'CM', 59.40000153)],\n",
       " [(703, 'FM', 271.2000122),\n",
       "  (703, 'FM', 274.10000610000003),\n",
       "  (703, 'FM', 276.39999389999997),\n",
       "  (703, 'FM', 279.10000610000003)],\n",
       " [(716, 'CM', 111.09999850000001),\n",
       "  (716, 'FC', 112.8000031),\n",
       "  (716, 'FM', 113.40000149999999),\n",
       "  (716, 'FL', 115.90000149999999)],\n",
       " [(717, 'FM', 198.6999969),\n",
       "  (717, 'CM', 201.5),\n",
       "  (717, 'FS', 203.8000031),\n",
       "  (717, 'FM', 206.5)],\n",
       " [(775, 'CL', 155.6999969),\n",
       "  (775, 'CM', 158.5),\n",
       "  (775, 'CM', 160.5),\n",
       "  (775, 'CM', 163.3999939)],\n",
       " [(786, 'CL', 176.8999939),\n",
       "  (786, 'CC', 177.6999969),\n",
       "  (786, 'CL', 179.3999939),\n",
       "  (786, 'CM', 179.8000031)],\n",
       " [(944, 'CM', 115.40000149999999),\n",
       "  (944, 'FM', 117.40000149999999),\n",
       "  (944, 'JOM', 118.90000149999999),\n",
       "  (944, 'JOM', 121.5)],\n",
       " [(945, 'FM', 243.10000609999997),\n",
       "  (945, 'FL', 244.5),\n",
       "  (945, 'FM', 246.1999969),\n",
       "  (945, 'FL', 248.89999390000003)],\n",
       " [(988, 'CM', 113.8000031),\n",
       "  (988, 'CC', 116.5),\n",
       "  (988, 'FC', 118.0),\n",
       "  (988, 'FC', 119.40000149999999)],\n",
       " [(1010, 'FM', 262.2000122),\n",
       "  (1010, 'CM', 265.1000061),\n",
       "  (1010, 'FM', 268.0),\n",
       "  (1010, 'CL', 270.10000610000003)],\n",
       " [(1013, 'FL', 65.40000153),\n",
       "  (1013, 'FL', 68.09999847),\n",
       "  (1013, 'FM', 70.40000153),\n",
       "  (1013, 'FM', 73.30000305)],\n",
       " [(1106, 'CM', 104.6999969),\n",
       "  (1106, 'CM', 107.59999850000001),\n",
       "  (1106, 'CM', 109.59999850000001),\n",
       "  (1106, 'CL', 112.3000031)],\n",
       " [(1142, 'CC', 1.399999976),\n",
       "  (1142, 'CM', 3.700000048),\n",
       "  (1142, 'CM', 6.599999905),\n",
       "  (1142, 'CC', 8.399999618999999)],\n",
       " [(1150, 'CM', 38.40000153),\n",
       "  (1150, 'CL', 41.20000076),\n",
       "  (1150, 'CM', 43.40000153),\n",
       "  (1150, 'CM', 46.20000076)],\n",
       " [(1245, 'CL', 234.3000031),\n",
       "  (1245, 'CL', 234.3000031),\n",
       "  (1245, 'CM', 235.89999390000003),\n",
       "  (1245, 'CM', 235.89999390000003)],\n",
       " [(1396, 'FC', 53.79999924),\n",
       "  (1396, 'FC', 53.79999924),\n",
       "  (1396, 'FC', 56.0),\n",
       "  (1396, 'FC', 58.90000153)],\n",
       " [(1484, 'FC', 158.3999939),\n",
       "  (1484, 'FM', 160.1999969),\n",
       "  (1484, 'FM', 162.3999939),\n",
       "  (1484, 'FM', 164.1999969)],\n",
       " [(1526, 'CC', 69.0),\n",
       "  (1526, 'FL', 71.5),\n",
       "  (1526, 'FC', 71.90000153),\n",
       "  (1526, 'FM', 74.80000305)],\n",
       " [(1659, 'FC', 136.1000061),\n",
       "  (1659, 'CL', 136.1000061),\n",
       "  (1659, 'FL', 139.0),\n",
       "  (1659, 'FC', 141.8999939)],\n",
       " [(1660, 'CC', 28.29999924),\n",
       "  (1660, 'CL', 30.10000038),\n",
       "  (1660, 'CM', 31.39999962),\n",
       "  (1660, 'FM', 33.90000153)],\n",
       " [(1702, 'CL', 54.40000153),\n",
       "  (1702, 'CC', 56.90000153),\n",
       "  (1702, 'FL', 58.0),\n",
       "  (1702, 'FL', 58.5)],\n",
       " [(1765, 'FM', 86.30000305),\n",
       "  (1765, 'CM', 87.30000305),\n",
       "  (1765, 'FM', 89.30000305),\n",
       "  (1765, 'FM', 92.19999695)]]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_zones = [zone for zone in zones if len(zone) == 4]\n",
    "tot_severity = 0\n",
    "num_zones = 0\n",
    "max_severity = 0\n",
    "max_zone = []\n",
    "min_severity = 1000\n",
    "min_zone = []\n",
    "\n",
    "for zone in filtered_zones:\n",
    "    zone_severity = calc_severity_zone(zone)\n",
    "    tot_severity  += zone_severity\n",
    "    num_zones += 1 \n",
    "    if zone_severity > max_severity:\n",
    "        max_severity = zone_severity\n",
    "        max_zone = zone\n",
    "    if zone_severity < min_severity:\n",
    "        min_severity = zone_severity\n",
    "        min_zone = zone\n",
    "\n",
    "print(f'Average severity is {tot_severity/num_zones}')\n",
    "print(f'Maximum severity is {max_severity}')\n",
    "# print(f'Maximum severity zone is {max_zone}')\n",
    "print(f'Minimum severity is {min_severity}')\n",
    "# print(f'Minimum severity zone is {min_zone}')\n",
    "filtered_zones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Most frequent defect in colocated zone\n",
    "\"\"\"\n",
    "defects = {defect_code:0 for defect_code in keep_defects}\n",
    "for zone in zones:\n",
    "    if len(zone) >= 3:\n",
    "        for defect in zone:\n",
    "            defects[defect[1]] += 1 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "defects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Finding severity of defect clusters and finding length of these clusters.\n",
    "Will allow for the best patch repair\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Interdefect association rule mining\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create matrix: https://towardsdatascience.com/association-rule-mining-be4122fc1793\n",
    "import numpy as np\n",
    "\n",
    "co_matrix = []\n",
    "\n",
    "for zone in zones:\n",
    "    row = np.zeros(len(keep_defects))\n",
    "    for _, defect, _ in zone:\n",
    "        defect_index = keep_defects.index(defect)\n",
    "        row[defect_index] = 1\n",
    "    co_matrix.append(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "zones[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(co_matrix)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "co_matrix[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(co_matrix[0]) == len(keep_defects)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(co_matrix[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def count_pairs(x, y, keep_defects, co_matrix):\n",
    "    count_x, count_y, count_xy = 0, 0, 0\n",
    "    for row in co_matrix:\n",
    "        if row[x] == 1 and row[y] == 1.0:\n",
    "            count_xy += 1\n",
    "        if row[x] == 1.0:\n",
    "            count_x += 1\n",
    "        if row[y] == 1.0:\n",
    "            count_y += 1\n",
    "            \n",
    "    return count_x, count_y, count_xy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def support_confidence_xy(x, y, keep_defects, co_matrix):\n",
    "    count_x, count_y, count_xy = count_pairs(x, y, keep_defects, co_matrix)\n",
    "    support_xy = count_xy/len(co_matrix)\n",
    "    support_x = count_x/len(co_matrix)\n",
    "    support_y = count_y/len(co_matrix)\n",
    "    if count_x ==0 or count_y == 0 or count_xy ==0:\n",
    "        confidence_xy = 0\n",
    "    else:\n",
    "        confidence_xy = count_xy/count_x\n",
    "    return support_x, support_y, support_xy, confidence_xy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "min_support = 0.001\n",
    "min_confidence = 0.1\n",
    "rules = []\n",
    "\n",
    "for x in range(0, len(keep_defects)):\n",
    "    for y in range(0, len(keep_defects)):\n",
    "        if x!=y:\n",
    "            support_x, support_y, support_xy, confidence_xy = support_confidence_xy(x, y, keep_defects, co_matrix)\n",
    "            if support_xy > min_support and confidence_xy > min_confidence:\n",
    "                lift_xy = support_xy/(support_x*support_y)\n",
    "                rules.append([keep_defects[x], keep_defects[y], support_xy, confidence_xy, lift_xy])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rules.sort(key=lambda tup: tup[3], reverse=True)  # sorts in place by confidence\n",
    "rules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\" \n",
    "\n",
    "Interesting Rules from Association Rules on Pipe-Level \n",
    "\n",
    "['JOL', 'FM', 0.011782032400589101, 0.5384615384615384, 2.213933989885547]\n",
    "['RFB', 'FM', 0.014727540500736377, 0.6542056074766355, 2.689826342851599],\n",
    "['RFB', 'CL', 0.011782032400589101, 0.5233644859813084, 2.318314447221956],\n",
    "\n",
    "Interesting Rules from Association Rules on Cluster-Level (Threshold = 20) \n",
    "['JOM', 'FM', 0.019923103809856693, 0.24332977588046958, 1.5513459360312503],\n",
    "['JOM', 'CL', 0.01896190143306536, 0.23159018143009605, 1.6111355843684008]\n",
    "['JOM', 'CM', 0.01494232785739252, 0.1824973319103522, 1.4917853331300504]]\n",
    "\n",
    "Interesting Rules from Association Rules on Cluster-Level (Threshold = 30) \n",
    "['HSV', 'FM', 0.01151047887935715, 0.6198830409356725, 3.8209524256871545],\n",
    "['BSV', 'FM', 0.01205342599630796, 0.5522388059701493, 3.40399408579592],\n",
    "['JOM', 'FM', 0.0253013356499077, 0.2757396449704142, 1.6996562185626136],\n",
    "['JOM', 'CL', 0.023672494299055272, 0.2579881656804734, 1.7291215558598831],\n",
    "['CS', 'JOM', 0.011619068302747313, 0.25235849056603776, 2.7502595735179187],\n",
    "['JOM', 'CM', 0.021609295254642197, 0.23550295857988165, 1.9040796712573573]\n",
    "['FS', 'JOM', 0.013465088500380064, 0.20261437908496732, 2.2081370615307265]\n",
    "\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_rows = 0\n",
    "for row in co_matrix:\n",
    "    if np.count_nonzero(row == 1) > 1:\n",
    "        num_rows += 1\n",
    "        \n",
    "print(f'Number of rows with more than 1 defect is {num_rows}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lift = support/(support_x*support_y)\n",
    "lift"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "support"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Make a database with external factors for econometric analysis\n",
    "\"\"\"\n",
    "# Read the inspections database\n",
    "df_insp = pd.read_csv('Inspections_Hazen_Sawyer.csv')\n",
    "\n",
    "# Columns that we want to keep\n",
    "cols = ['InspectionID', 'Height', 'Down_Rim_to_Invert', 'Up_Rim_to_Invert', 'Height', 'Total_Length', 'Location_Code', 'Location_Details', 'Material', 'Weather', 'Street']\n",
    "\n",
    "# Defect pairs to keep\n",
    "keep_defects = ['Fracture', 'Crack', 'Tap', 'Manhole', 'Joint Offset', 'Root', 'Deposit', 'Broken']\n",
    "\n",
    "df_insp = create_econometric_database(df_insp, cols, keep_defects)\n",
    "\n",
    "df_insp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
